// express Node.js Web应用框架
const express = require("express");
const bodyParser = require("body-parser"); // 处理post请求传递过来的数据
const multiparty = require("connect-multiparty");
const request = require("request");
const {log, error} = require("console");
const cors = require("cors"); // 解决跨域
const server = express(); // 创建服务
const {v4: uuid} = require('uuid')

server.use(bodyParser.json()).use(cors()).use(multiparty()).use(bodyParser.urlencoded({extend: true}));
server.listen(9000, () => {
    console.log("server is running.....");
});

// 开发者信息
const wx = {
    appid: "wxb9fa30a27e974f39",
    appSecret: "4e6ad74c9db477ae25bc078dd27f511d"
};
const db = {
    user: {},
    session: {}
};
// 模拟数据库存储数据，要创建数据库并连接存储在数据库中

// 导入数据库函数
const {conMysql} = require("./mysql");
const {resolve, format} = require("path");
// 创建统一的返回报文对象
class Response {
    constructor(isSucceed, msg, code, data) {
        (this.isSuccee = isSucceed),

        (this.msg = msg),
        (this.code = code),
        (this.data = data);
    }
} server.post("/login", (req, res) => {
    console.log("code:" + req.body.code);
    // 请求到微信接口服务校验
    var url = `https://api.weixin.qq.com/sns/jscode2session?appid=${
        wx.appid
    }&secret=${
        wx.appSecret
    }&js_code=${
        req.body.code
    }&grant_type=authorization_code`;
    request(url, (err, response, body) => {
        // 通过响应体body获取用户唯一标识 openid 回话记录session_key
        // 转字符串为js对象
        const session = JSON.parse(body);
        console.log("session:", session);
        if (session.openid) {
            var token = "token_" + new Date().getTime(); // 时间戳作为用户登录的token
            db.session[token] = session;
            if (! db.user[session.openid]) {
                db.user[session.openid] = {
                    state: 1, // 存储用户的信息，登录成功可以获取，可以用于判断用户的状态
                };
            }
            console.log(db);
        }
        res.json({token: token});
    });
});
// 判断数据库中session中的token是否存在，否则token已失效
server.get("/checklogin", (req, res) => {
    var session = db.session[req.query.token];
    console.log(session);
    res.json({
        is_login: session !== undefined
    });
});
server.get("/test", (req, res) => { // 查询数据库
    let sql = "select * from test";
    conMysql(sql).then((result) => {
        res.send(result);
    });
    // res.send(result)
});

// 根据前端传递参数查询数据库中拿到信息
// server.get('/getUserInfo',(req,res)=>{
//     let sql=`select * from user where id='${req.query.id}'`
//     conMysql(sql).then(result=>{
//         let response=new Response(true,'获取用户信息成功',200,result)
//         res.send(response)
//     }).catch(err=>{
//         res.status(500).send('数据库连接出错！')
//     })
// })
// 获取全部楼盘信息
server.get("/getAllLlift", (req, res) => { // 实现分页
    const {page, pageSize} = req.query
    // console.log(req.query);
    let sql = `select * from loupan`;
    // console.log(sql);
    conMysql(sql).then((result) => {
        const total = result.length
        let sql2 = `select * from loupan LIMIT ${pageSize} OFFSET ${
            (page - 1) * pageSize
        }`;
        conMysql(sql2).then((result2) => {
            let response = new Response(true, "获取当前页面楼盘信息成功！", 200, {
                pagelist: result2,
                total
            });
            res.send(response);
        }).catch((err) => {
            res.status(500).send("数据库连接出错！");
        })

    }).catch((err) => {
        console.log(err);
        res.status(500).send("数据库连接出错！");
    });
});
// 修改楼盘投放状态
server.get("/changeliftstatus", (req, res) => {
    let sql = `select * from loupan where id='${
        req.query.id
    }'`;
    conMysql(sql).then((result) => {
        const sql2 = result[0].status === '0' ? `UPDATE loupan  SET status = 1  WHERE id = '${
            req.query.id
        }'` : `UPDATE loupan  SET status = 0  WHERE id = '${
            req.query.id
        }'`
        conMysql(sql2).then((result2) => {
            let response = new Response(true, "修改楼盘状态成功！", 200, result2);
            res.send(response);
        }).catch((err) => {
            res.status(500).send(err);
        });
    }).catch((err) => {
        console.log(err);
        res.status(500).send("数据库连接出错！");
    });
});
// 获取可投放楼盘
server.get("/getliftInfo", (req, res) => {
    let sql = "select * from loupan where status='1'";
    conMysql(sql).then((result) => {
        let response = new Response(true, "获取可投放楼盘信息成功！", 200, result);
        res.send(response);
    }).catch((err) => { // console.log(err);
        res.status(500).send("数据库连接出错！");
    });
});
// 新增楼盘
// 批量删除楼盘
server.get('/deleteloupanArr', (req, res) => { // let index,response
    const deleteArr = req.query.idArr
    // 使用参数化查询避免SQL依赖注入
    const sqlPromises = deleteArr.map(id => {
        return conMysql(`delete from loupan where id=${id}`)
    })
    Promise.all(sqlPromises).then((result) => {
        res.send(new Response(true, "删除楼盘成功", 200, result))
    }).catch(err => { // console.log(err);
        res.status(500).send('数据库连接出错！')

    })

})
// 管理系统条件查询条件查询楼盘
server.get('/selectLoupan', async (req, res) => {
    const {
        type,
        name,
        status,
        page,
        pageSize
    } = req.query

    let sql = `select * from loupan where 1=1 `
    if (type) 
        sql = sql + `and type='${type}' `
    if (name) 
        sql = sql + `and  name LIKE '%${name}%' `
    if (status) 
        sql = sql + `and status='${status}'`
    // console.log(sql);
    conMysql(sql).then((result) => {
        const total = result.length
        let sql2 = sql + ` LIMIT ${pageSize} OFFSET ${
            (page - 1) * pageSize
        }`;
        conMysql(sql2).then((result2) => {
            let response = new Response(true, "条件查询获取当前页面楼盘信息成功！", 200, {
                pagelist: result2,
                total
            });
            res.send(response);
        }).catch((err) => {
            res.status(500).send("数据库连接出错！");
        })


    }).catch((err) => {
        console.log(err);
        res.status(500).send("数据库连接出错！");

    })


})
// 登录校验
server.get("/checkInfo", (req, res) => { // console.log(req.body);

    let sql = `select * from user where phone='${
        req.query.phone
    }' and status != '-1'`;

    if (req.query.type && req.query.type === 'admin') {
        sql += ` and role = '0'`
    }

    conMysql(sql).then((result) => { // console.log(result,typeof(result[0].status));
        let response;
        if (result.length === 0) {
            response = new Response(false, "账号不存在，无权限登陆", 200, result);

        } else if (result[0] && result[0].status !== '1') {
            response = new Response(false, "登录失败，该账号被停用，请联系其他管理员", 200, result);
        } else if (result[0] && result[0].password !== req.query.password) {
            response = new Response(false, "登录失败，账号或密码错误", 200, result);
        } else if (result[0] && result[0].password === req.query.password) {
            response = new Response(true, "登录成功", 200, {token: uuid()});
        }
        res.send(response);
    }).catch((err) => {
        console.log(err);
        res.status(500).send("数据库连接出错！");
    });
});
// 注册账号
server.get("/register", (req, res) => {
    let response;
    // 校验账号是否存在
    let sql = `select * from user where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => {
        if (result.length !== 0) {
            response = new Response(true, "账号已存在!", 201, result);
        } else {
            let sql2 = `INSERT INTO user (phone,password) VALUES ('${
                req.query.phone
            }', '${
                req.query.password
            }')`;
            conMysql(sql2).then((result) => {
                response = new Response(true, "注册成功", 200);
            }).catch((err) => {
                res.status(500).send(err);
            });
        }
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 提交个人信息
server.get("/submitInfo", (req, res) => {
    let sql = `update user 
    set name='${
        req.query.name
    }',
    wxName='${
        req.query.wxName
    }',
    avatarUrl='${
        req.query.avatarUrl
    }',
    email='${
        req.query.email
    }',
    enterpriseName='${
        req.query.enterpriseName
    }',
    address='${
        req.query.address
    }'
     where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "保存成功！", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 获取个人信息
server.get("/getUserInfo", (req, res) => {
    let sql = `select * from user where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "获取个人信息成功", 200, result);
        console.log(sql,response);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 获取头像url
server.get("/getAvatarUrl", (req, res) => {
    let sql = `select avatarUrl from user where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "获取用户头像成功", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 加入购物车
server.get("/addShopCar", (req, res) => {
    let sql = `INSERT INTO shopCar (hasCheckWeek,sucaiType,time,liftNumber,name,address,totalPrice,shopNumber,phone,imgurl)
     VALUES ('${
        req.query.hasCheckWeek
    }','${
        req.query.sucaiType
    }','${
        req.query.time
    }','${
        req.query.liftNumber
    }','${
        req.query.name
    }','${
        req.query.address
    }','${
        req.query.totalPrice
    }','${
        req.query.shopNumber
    }','${
        req.query.phone
    }','${
        req.query.imgurl
    }')`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "加入购物车成功！", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 获取用户购物车数据
server.get("/getShopList", (req, res) => {
    let sql = `select * from shopCar where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "获取用户购物车列表成功", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 添加订单、
server.get("/setOrder", (req, res) => {
    let response;
    let sql = `INSERT INTO orders (hasCheckWeek,sucaiType,time,liftNumber,name,address,totalPrice,orderNumber,phone,orderTotalPrice,shopNumber)
    VALUES ('${
        req.query.hasCheckWeek
    }','${
        req.query.sucaiType
    }','${
        req.query.time
    }','${
        req.query.liftNumber
    }','${
        req.query.name
    }','${
        req.query.address
    }','${
        req.query.totalPrice
    }','${
        req.query.orderNumber
    }','${
        req.query.phone
    }','${
        req.query.orderTotalPrice
    }','${
        req.query.shopNumber
    }')`;
    conMysql(sql).then((result) => { // 删除购物车中的记录
        const sql1 = `DELETE FROM shopcar  
        WHERE phone = '${
            req.query.phone
        }' AND shopNumber = '${
            req.query.shopNumber
        }'`;
        conMysql(sql1).then((result) => {
            response = new Response(true, "提交订单成功！已删除购物车中的记录", 200, result);
        }).catch((err) => {
            res.status(500).send(err);
        });
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 获取用户订单数据
server.get("/getOrders", (req, res) => { // 先通过用户id查询获取用户所有的订单
    let sql = `select * from orders where phone='${
        req.query.phone
    }'`;
    // let sql=`select * from orders where phone='111'`
    const orderNumberarr = []; // 订单编号
    let orderShop = {}; // 订单中的产品
    const orderShoparr = []; // 订单数据
    conMysql(sql).then((result) => {
        result.forEach((item) => {
            orderNumberarr.includes(item.orderNumber) ? "" : orderNumberarr.push(item.orderNumber);
        });
        orderNumberarr.forEach((item) => {
            let sqlShop = `select * from orders where orderNumber='${item}'`;
            conMysql(sqlShop).then((result) => {
                orderShop[item] = result; // 订单编号：[商品数组]
            });
        });
        setTimeout(() => {
            let response = new Response(true, "获取订单信息成功", 200, orderShop);
            res.send(response);
        }, 500);
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 七牛云存储 素材
const qiniu = require("qiniu");
const {randomUUID} = require("crypto");
server.get("/getUpToken", (req, res) => {
    var accessKey = "VPHedAs_21doaPZVk4VGlXavhp_HDp0jYGIT9CFA";
    var secretKey = "QFp2gPoepb_Dm0Cn8qnldYlXxlNUvPawNFfebTTz";
    var mac = new qiniu.auth.digest.Mac(accessKey, secretKey);
    var options = {
        scope: "dd-lift", // 空间名称
    };
    var putPolicy = new qiniu.rs.PutPolicy(options);
    var uploadToken = putPolicy.uploadToken(mac);
    console.log(uploadToken);
    // 获取七牛云上传token
    // let response=new Response(true,'获取qiniu上传token成功',200,uploadToken)/
    const obj = {};
    obj.uptoken = uploadToken;
    obj.dimain = "scwmrx2nv.hn-bkt.clouddn.com"    ;
    res.send(obj);
});

// 支付
server.get("/pay", (req, res) => {
    let sql = `UPDATE orders  
SET pay = '1'  
where phone='${
        req.query.phone
    }' and orderNumber='${
        req.query.orderNumber
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "支付成功！", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 支付成功后添加待关联素材订单
server.get("/putSucaiOrder", (req, res) => {
    let sql = `INSERT INTO source (phone,sucaiType,liftNumber,name,orderNumber)
    VALUES ('${
        req.query.phone
    }','${
        req.query.sucaiType
    }','${
        req.query.liftNumber
    }','${
        req.query.name
    }','${
        req.query.orderNumber
    }')`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "提交订单成功！", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});
// 获取用户素材信息
server.get("/getSucaiOrder", (req, res) => {
    let sql = `select * from source where phone='${
        req.query.phone
    }'`;
    conMysql(sql).then((result) => { // 已关联 未审核
        const hasAssociated = result.filter((item) => item.sourceurl !== '0' && item.reviewstatus === "0");
        // 未关联 或关联审核失败
        const tobeAssociated = result.filter((item) => item.sourceurl === '0');
        // 审核未通过 需要重新关联
        const Reviewfailed = result.filter((item) => item.reviewstatus === "2");
        // 审核通过 待发布
        const Approved = result.filter((item) => item.reviewstatus === "1");
        // 发布完成
        const hasShow = result.filter((item) => item.reviewstatus === "4");

        const result1 = {
            hasAssociated,
            tobeAssociated,
            Reviewfailed,
            Approved,
            hasShow
        };

        let response = new Response(true, "获取素材订单信息成功！", 200, result1);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 添加了关联素材 url  修改状态未未审核 针对审核失败再次关联素材的情况
server.get('/addSourceUrl', (req, res) => {
    let sql = `UPDATE source  
SET sourceurl='${
        req.query.url
    }', reviewstatus='0'
where id='${
        req.query.id
    }'`;
    conMysql(sql).then((result) => {
        let response = new Response(true, "素材关联成功！", 200, result);
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err)
    })
});

/**
 * 管理端
 */
// 小程序用户管理
// 获取小程序用户列表
server.post("/miniuser/getUserList", (req, res) => { // 获取请求体中的页码和每页数据量
    const {
        pageNum = 1,
        pageSize = 10,
        keyword = ''
    } = req.body;

    // 计算数据偏移量
    const offset = (pageNum - 1) * pageSize;
    // 查询条件
    const queryCondition = ` AND phone LIKE '%${keyword}%' OR name LIKE '%${keyword}%'`

    let querySql = `SELECT * FROM user WHERE role = '1'`
    if (keyword) 
        querySql += queryCondition


    


    querySql += ` LIMIT ${offset}, ${pageSize}`

    let countSql = `SELECT COUNT(*) AS total FROM user WHERE role = '1' `
    if (keyword) 
        countSql += queryCondition


    


    Promise.all([
        conMysql(querySql), // 查集合
        conMysql(countSql) // 查条数
    ]).then(([resArr, resCount]) => {
        let response = new Response(true, "获取用户列表数据成功", 200, {
            list: resArr,
            total: resCount[0].total
        });
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });

});
// 修改小程序用户状态
server.post("/miniuser/changeMiniUserStatus", (req, res) => { // 获取请求体中的页码和每页数据量
    const {id, status} = req.body;
    if (!id || !status) {
        res.send(new Response(true, "必传参数为空", 200, {}));
    }
    let sql = `UPDATE user SET status = '${status}' WHERE id = '${id}'`
    conMysql(sql).then((result) => {
        res.send(new Response(true, "操作成功", 200, {}));
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 获取管理端用户列表
server.post("/adminuser/getUserList", (req, res) => { // 获取请求体中的页码和每页数据量
    const {
        pageNum = 1,
        pageSize = 10,
        keyword = ''
    } = req.body;

    // 计算数据偏移量
    const offset = (pageNum - 1) * pageSize;
    // 查询条件
    const queryCondition = ` AND name LIKE '%${keyword}%'`

    let querySql = `SELECT * FROM user WHERE role = '0'`
    if (keyword) 
        querySql += queryCondition


    


    querySql += ` LIMIT ${offset}, ${pageSize}`

    let countSql = `SELECT COUNT(*) AS total FROM user WHERE role = '0' `
    if (keyword) 
        countSql += queryCondition


    


    Promise.all([
        conMysql(querySql), // 查集合
        conMysql(countSql) // 查条数
    ]).then(([resArr, resCount]) => {
        let response = new Response(true, "获取用户列表数据成功", 200, {
            list: resArr,
            total: resCount[0].total
        });
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });

});
// 新增管理端用户
server.post("/adminuser/addAdminUser", (req, res) => {
    const {name, phone, avatarUrl} = req.body;

    // 查询用户是否已存在的 SQL 语句
    const checkUserSql = `SELECT * FROM user WHERE phone = '${phone}'`;
    conMysql(checkUserSql).then(result => {
        if (result.length > 0) {
            return res.send(new Response(false, "用户已存在", 200, []));
        } else {
            const insertUserSql = `INSERT INTO user (name, phone, avatarUrl, status, role, password) VALUES ('${name}', '${phone}', '${avatarUrl}', '1', '0', '123456')`;
            return conMysql(insertUserSql)
        }
    }).then(result => {
        if (result.affectedRows === 1) {
            return res.send(new Response(true, "新增成功", 200, []));
        } else {
            return res.send(new Response(false, "新增失败", 200, []));
        }
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 修改管理端用户
server.post("/adminuser/updateAdminUser", (req, res) => {
    const {
        id,
        name,
        phone,
        password,
        avatarUrl
    } = req.body;

    const updateUserSql = `UPDATE user set name = '${name}', phone = '${phone}', avatarUrl= '${avatarUrl}', password= '${password}' WHERE id = '${id}' `;
    conMysql(updateUserSql).then(result => {
        console.log(result, '修改');
        if (result.affectedRows === 1) {
            return res.send(new Response(true, "修改成功", 200, []));
        } else {
            return res.send(new Response(false, "修改失败", 200, []));
        }
    }).catch((err) => {
        res.status(500).send(err);
    });
});

// 重置管理员密码
server.post("/adminuser/resetAdminUserPsw", (req, res) => {
    const {id} = req.body;

    const updateUserSql = `UPDATE user set password= '123456' WHERE id = '${id}' `;
    conMysql(updateUserSql).then(result => {
        console.log(result, '修改');
        if (result.affectedRows === 1) {
            return res.send(new Response(true, "重置成功", 200, []));
        } else {
            return res.send(new Response(false, "重置失败", 200, []));
        }
    }).catch((err) => {
        res.status(500).send(err);
    });
});


// 订单管理 分页查询订单列表
server.post("/order/fetchList", (req, res) => { // 获取请求体中的页码和每页数据量
    const {
        pageNum = 1,
        pageSize = 10,
        orderNumber = '',
        phoneOrwxName = '',
        liftNumber = '',
        pay = ''
    } = req.body;

    // 计算数据偏移量
    const offset = (pageNum - 1) * pageSize;
    // 查询条件
    const queryCondition = ` o.delCd = '1' AND o.orderNumber LIKE "%${orderNumber}%" AND ( o.phone LIKE "%${phoneOrwxName}%" OR u.wxName LIKE "%${phoneOrwxName}%") AND o.liftNumber LIKE "%${liftNumber}%" AND o.pay LIKE "%${pay}%"`

    let querySql = `SELECT o.*, u.name, u.phone, u.wxName FROM orders o JOIN user u ON o.phone = u.phone WHERE `
    querySql += queryCondition
    querySql += ` LIMIT ${offset}, ${pageSize}`

    let countSql = `SELECT COUNT(*) AS total FROM orders o JOIN user u ON o.phone = u.phone WHERE `
    countSql += queryCondition

    console.log(querySql, countSql, 12321);

    Promise.all([
        conMysql(querySql), // 查集合
        conMysql(countSql) // 查条数
    ]).then(([resArr, resCount]) => {
        let response = new Response(true, "获取订单数据成功", 200, {
            list: resArr,
            total: resCount[0].total
        });
        res.send(response);
    }).catch((err) => {
        res.status(500).send(err);
    });

});

const {pool} = require("./mysql");
server.post("/order/deleteOrder", async (req, res) => {
    try {
        const {ids} = req.body;
        if (!Array.isArray(ids) || ids.length === 0) {
            return res.status(400).send(new Response(false, "ids 必须是一个非空数组", 400, []));
        }

        // 使用连接池获取连接，并开启事务
        const connection = await pool.getConnection();
        await connection.beginTransaction();

        try { // 批量删除订单状态
            const sql = 'UPDATE orders SET delCd = ? WHERE keyId IN (?)';
            const idsToDelete = ids.map(id => connection.escape(id));
            await connection.query(sql, ['0', idsToDelete]);

            // 提交事务
            await connection.commit();
            return res.send(new Response(true, "删除成功", 200, []));
        } catch (err) { // 回滚事务
            await connection.rollback();
            throw err;
        } finally {
            connection.release();
        }
    } catch (err) {
        return res.send(new Response(false, "删除失败", 200, []));
    }
});

// 添加楼盘信息
server.post("/loupan/addloupan", async (req, res) => { // console.log(req.body);
    const {
        Cheekin,
        CheekinPercent,
        Rent,
        address,
        latitude,
        liftArr,
        longitude,
        name,
        noType,
        price,
        price5,
        price10,
        size,
        tall,
        type,
        url,
        lift
    } = req.body
    let sql = `INSERT INTO loupan (Cheekin,CheekinPercent,Rent,address,latitude,liftArr,longitude,name,noType,price,price5,price10,size,tall,type,url,lift) 
    values ('${Cheekin}','${CheekinPercent}','${Rent}','${address}','${latitude}','${liftArr}','${longitude}','${name}','${noType}','${price}','${price5}','${price10}','${size}','${tall}','${type}','${url}','${lift}')`
    conMysql(sql).then((result) => {
        let response = new Response(true, '添加楼盘成功', 200)
        res.send(response)
    }).catch((err) => {
        res.status(500).send(err)
    })


})
// 根据id 获取楼盘详情
server.get("/loupan/getloupan", async (req, res) => {
    let sql = `select * from loupan where id='${
        req.query.id
    }'`
    conMysql(sql).then((result) => {
        res.send(new Response(true, '获取楼盘信息成功', 200, result))
    }).catch((err) => {
        res.status(500).send(err)
    })
})
// 修改楼盘信息
server.post("/loupan/changeloupan", async (req, res) => {
    const {
        Cheekin,
        CheekinPercent,
        Rent,
        address,
        latitude,
        liftArr,
        longitude,
        name,
        noType,
        price,
        price5,
        price10,
        size,
        tall,
        type,
        url,
        lift
    } = req.body.loupan
    const sql = `UPDATE loupan set Cheekin='${Cheekin}',CheekinPercent='${CheekinPercent}',Rent='${Rent}',address='${address}',latitude='${latitude}',liftArr='${liftArr}',longitude='${longitude}',name='${name}',noType='${noType}',price='${price}',price5='${price5}',price10='${price10}',size='${size}',tall='${tall}',type='${type}',url='${url}',lift='${lift}'  where id='${
        req.body.id
    }' `
    conMysql(sql).then((result) => {
        res.send(new Response(true, '修改楼盘信息成功', 200, result))
    }).catch((err) => {
        res.status(500).send(err)
    })
})
// 获取未审核订单 0未审核 2 未通过
server.get("/source/getSourcNot", (req, res) => {
    console.log(req.query);
    const {pageNum, pageSize,loupanName,orderNumber} = req.query
    let sql1=''

    if(loupanName){
sql1=sql1+`and name LIKE '%${loupanName}%' `
    }
    if(orderNumber){
sql1=sql1+`and orderNumber LIKE '%${orderNumber}%'`
        
    }
    const sql2 = `select * from source  where (reviewstatus="0" or reviewstatus="2") and (sourceurl != "0") ${sql1} `
    const sql3=sql2+`LIMIT ${pageSize} OFFSET ${ (pageNum - 1) * pageSize } `
    // console.log('sql2',sql2);
    // console.log('sql',sql);

    conMysql(sql2).then((result) => {
        const total = result.length
        conMysql(sql3).then(result2=>{
            res.send(new Response(true, '获取未审核素材订单成功', 200, {total, list:result2}))

        }).catch(err=>{
            res.status(500).send(err)
        })
    }).catch((err) => {
        res.status(500).send(err)
    })
})
// 修改审核状态
server.post("/source/updateStatus",(req,res)=>{
    //只有feedbackurl才会将订单审核状态改为4 已投放
    const {id,reviewstatus,idea}=req.body
    // console.log({id,reviewstatus,idea});
    const sql=`update source set reviewstatus='${reviewstatus}' ,  idea='${idea}' where id='${id}'`
    conMysql(sql).then((result)=>{
        res.send(new Response(true,'修改审核状态成功！',200,result))
    }).catch((err)=>{
        res.status(500).send(err)
    })
})
// 获取审核通过素材订单
server.get("/source/getSourceYes", (req, res) => {
    // console.log(req.query);
    const {pageNum, pageSize,loupanName,orderNumber,feedbackStatus} = req.query
    let sql1=''
    if(loupanName){
sql1=sql1+`and name LIKE '%${loupanName}%' `
    }
    if(orderNumber){
sql1=sql1+`and orderNumber LIKE '%${orderNumber}%'`
        
    }

    let sql2=`reviewstatus="1" or reviewstatus="4"`
    if(feedbackStatus==='4'){
        //已投放
    sql2=`reviewstatus="4"`
    }else if(feedbackStatus==='1'){
    sql2=`reviewstatus="1"`
    }
    const sql3 = `select * from source  where (sourceurl != "0") and (${sql2}) ${sql1}`
    const sql4=sql3+`LIMIT ${pageSize} OFFSET ${(pageNum - 1) * pageSize }`
    // console.log(sql4);
    conMysql(sql3).then((result) => {
        // 已绑定素材
    //    const result11 = result.filter((item) => item.sourceurl !== '0' || item.sourceurl !== '');
    //    console.log(result11 );
        const total = result.length
        conMysql(sql4).then(result2=>{
    //    const result22 = result2.filter((item) => item.sourceurl !== '0' || item.sourceurl !== '');

            res.send(new Response(true, '获取已审核素材订单成功', 200, {total, list:result2}))

        }).catch((err) => {
            res.status(500).send(err)
        })
    }).catch((err) => {
        res.status(500).send(err)
    })
})
// 上传反馈图片
server.post("/source/addFeedBackImg",(req,res)=>{
    // console.log(req.query);
    const sql=`UPDATE source SET feedbackUrl='${req.body.url}', reviewstatus='4' WHERE  id='${req.body.id}'`
    conMysql(sql).then(result=>{
        res.send(new Response(true,'反馈成功',200,result))
    }).catch(err=>{
        res.status(500).send(err)
    })
})

// 管理端 查询首页数据
server.post("/home/getStatData", (req, res) => {  
    const loupanSql = `SELECT
    (SELECT COUNT(*) FROM loupan) AS total_loupan,
    (SELECT COUNT(*) FROM loupan WHERE status = 1) AS Ad_loupan,
    (SELECT COUNT(*) FROM loupan WHERE status = 0) AS noAd_loupan,
    (SELECT COUNT(*) FROM orders) AS total_orders,
    (SELECT COUNT(*) FROM orders WHERE pay = 1) AS pay_orders,
    (SELECT COUNT(*) FROM orders WHERE pay = 0) AS noPay_orders,
    (SELECT COUNT(*) FROM source WHERE reviewstatus = 0) AS noexam_source,
    (SELECT COUNT(*) FROM source WHERE reviewstatus = 1 OR reviewstatus = 4) AS pass_source,
    (SELECT COUNT(*) FROM source WHERE reviewstatus = 2) AS nopass_source,
    (SELECT GROUP_CONCAT(DISTINCT sucaiType) FROM orders) AS sucaiTypes,
    (SELECT GROUP_CONCAT(num) FROM (SELECT COUNT(*) AS num FROM orders GROUP BY sucaiType) AS t) AS sucaiTypeCounts,
    (SELECT GROUP_CONCAT(DISTINCT name) FROM orders) AS names,
    (SELECT GROUP_CONCAT(num) FROM (SELECT COUNT(*) AS num FROM orders GROUP BY name) AS t) AS nameCounts; `
   
    // (SELECT GROUP_CONCAT(DISTINCT name) FROM orders) AS names 获取订单中所有楼盘的名字
    // (SELECT GROUP_CONCAT(num) FROM (SELECT COUNT(*) AS num FROM orders GROUP BY name) AS t) AS nameCounts  获取订单中各个楼盘被下单的次数
   
    conMysql(loupanSql).then(result => {
        if (result.length > 0) {
            let resResult = result.map(r => ({
                ...r,
                nameCounts: r.nameCounts.split(','),//被下单次数最多的前五个 数量
                names: r.names.split(','),//楼盘名称
                sucaiTypeCounts: r.sucaiTypeCounts.split(','),
                sucaiTypes: r.sucaiTypes.split(','),
                loupan5:r.loupan5
            }))[0]
           
         const sql2='SELECT name, COUNT(*) as count  FROM orders   WHERE name IS NOT NULL  GROUP BY name   ORDER BY count DESC   LIMIT 5'
         conMysql(sql2).then(result2=>{
            // console.log();
            // resResult.firstFive=result2
            const firstFiveName=[]
            const firstFiveNameCounts=[]
            result2.forEach(item=>{
firstFiveName.push(item.name)
firstFiveNameCounts.push(item.count)
            })
            // console.log(resResult.firstFive);
            resResult.firstFiveName=firstFiveName
            resResult.firstFiveNameCounts=firstFiveNameCounts
            return res.send(new Response(true, "查询成功", 200, resResult));

         }).catch((err) => {
            res.status(500).send(err);
          });
        } else {
            return res.send(new Response(false, "查询失败", 200, {}))
        }
    }).catch((err) => {
      res.status(500).send(err);
    });

});

// 评价部分
// 添加用户楼盘评价
server.post("/feedbackinfo/addfeedback",(req,res)=>{
    const {orderNumber,lou_name,feedbackidea,startNumber,user_phone:phone,sourceID}=req.body
    console.log('phone',phone);
    // 获取user_name
const sqlgetname=`SELECT * FROM user WHERE phone = '${phone}'`
conMysql(sqlgetname).then(result=>{
    console.log('user----',result);
    const {name : user_name,wxName :user_wxName}=result[0]
    const sql=`INSERT INTO feedbackinfo (orderNumber,lou_name,feedbackidea,startNumber,user_name,user_wxName,sourceID) values ('${orderNumber}','${lou_name}','${feedbackidea}','${startNumber}','${user_name}','${user_wxName}','${sourceID}')`
    conMysql(sql).then(result2=>{
        // 修改订单素材评价状态
        const changeStatus=`UPDATE source SET feedbackstatus='1' where id='${sourceID}'`
        conMysql(changeStatus).then(result3=>{
            res.send(new Response(true,'评价成功',200,result3))
        }).catch(err=>{
            res.status(500).send(err)
        })
    }).catch(err=>{
        res.status(500).send(err)
    })

}).catch(err=>{
    res.status(500).send(err)
})
})
// 获取当前订单素材楼盘的评价 展示
server.get("/feedbackinfo/getfeedbackInfo",(req,res)=>{
    const sql=`select * from feedbackinfo where sourceID='${req.query.sourceID}'`
    conMysql(sql).then(result=>{
        res.send(new Response(true,'获取评价成功',200,result))
    }).catch(err=>{
        res.status(500).send(err)
    })
})
// 获取楼盘所有评价信息
server.get("/feedbackinfo/getLoupanAllfeedbackInfo",(req,res)=>{
const sql=`select * from feedbackinfo where lou_name='${req.query.lou_name}'`
conMysql(sql).then(result=>{
    res.send(new Response(true,'获取楼盘评价信息成功',200,result))
}).catch(err=>{
    res.status(500).send(err)
})
})
// 添加或修改资质文件
server.post('/user/addfileUrl',(req,res)=>{
    const {fileUrl:fileurl,phone} = req.body
    // console.log(req.body);
    const sql=`update user set fileurl='${fileurl}' where phone=${phone}`
    conMysql(sql).then(result=>{
        res.send(new Response(true,'修改资质url成功',200,result))
    }).catch(err=>{
        res.status(500).send(err)
    })
})